﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HotelSoft.HotelManagement_DTO;
using System.Data;
using System.Collections;

namespace HotelSoft.HotelManagement_DAO
{
    class KhachHang_PhieuDangKyDAO
    {
        public static DataTable SelectKhachHang_PhieuDangKytheoSoPDK(object AbstractDTO)
        {
            KhachHang_PhieuDangKyDTO dto = (KhachHang_PhieuDangKyDTO)AbstractDTO;
            string sql = "SELECT mKH.CMND,mKH.HoTenKH,mKH.DiaChi,mKH.QuocTich,mKH_PDK.SoPDK" 
                + " FROM KHACHHANG_PHIEUDANGKY mKH_PDK, KHACHHANG mKH"
                + " Where mKH_PDK.CMND=mKH.CMND and mKH_PDK.SoPDK='" + dto.SoPDK + "'";
            DataTable ds1 = Config.AccessHelper.ExecuteQuery(sql);
            return Config.AccessHelper.ExecuteQuery(sql);
        }

        public static DataTable SelectKhachHang_PhieuDangKytheoSoPDKtongtien(object AbstractDTO)
        {
            int dem = 0;
            float tongtien = 0;
            KhachHang_PhieuDangKyDTO dto1 = (KhachHang_PhieuDangKyDTO)AbstractDTO;
            string sql1 = "SELECT mKH.CMND,mKH.HoTenKH,mKH.DiaChi,mKH.QuocTich,mKH_PDK.SoPDK"
                + " FROM KHACHHANG_PHIEUDANGKY mKH_PDK, KHACHHANG mKH"
                + " Where mKH_PDK.CMND=mKH.CMND and mKH_PDK.SoPDK='" + dto1.SoPDK + "'";
            DataTable ds1 = Config.AccessHelper.ExecuteQuery(sql1);
            int i = 0;
            int iSoDong = ds1.Rows.Count;
            int iSoCot = ds1.Columns.Count;
            while (i < iSoDong)
            {
                if (ds1.Rows[i]["QuocTich"].ToString() != "Việt Nam")
                    dem++;
                i++;
            }

            string sql2 = "Select * from HESO_TILE";
            DataTable ds2 = Config.AccessHelper.ExecuteQuery(sql2);

            string sql3 = "SELECT mPDK.SoPDK,mPDK.SoNgayThue,mPDK.SoPhong,mPDK.TraTruoc,mP.TenLP,mLP.Gia"
                + " FROM PHIEUDANGKY mPDK, PHONG mP, LOAIPHONG mLP"
                + " Where mPDK.SoPDK='" + dto1.SoPDK + "' and mPDK.SoPhong=mP.SoPhong and mP.TenLP=mLP.TenLP ";
            DataTable ds3 = Config.AccessHelper.ExecuteQuery(sql3);

            if (iSoDong < 3)
            {
                if (dem == 0)
                {
                    tongtien = (float)int.Parse(ds3.Rows[0]["SoNgayThue"].ToString()) * float.Parse(ds3.Rows[0]["Gia"].ToString()) - float.Parse(ds3.Rows[0]["TraTruoc"].ToString());
                }
                else
                {
                    tongtien = (float)int.Parse(ds3.Rows[0]["SoNgayThue"].ToString()) * float.Parse(ds3.Rows[0]["Gia"].ToString()) * float.Parse(ds2.Rows[0]["HS_KhachNcNgoai"].ToString()) - float.Parse(ds3.Rows[0]["TraTruoc"].ToString());
                }
            }
            else
            {
                if (dem == 0)
                {
                    tongtien = (float)int.Parse(ds3.Rows[0]["SoNgayThue"].ToString()) * float.Parse(ds3.Rows[0]["Gia"].ToString()) + ((float)int.Parse(ds3.Rows[0]["SoNgayThue"].ToString()) * float.Parse(ds3.Rows[0]["Gia"].ToString()) * float.Parse(ds2.Rows[0]["HS_KhachThu3"].ToString())) - float.Parse(ds3.Rows[0]["TraTruoc"].ToString());
                }
                else
                {
                    tongtien = (float)int.Parse(ds3.Rows[0]["SoNgayThue"].ToString()) * float.Parse(ds3.Rows[0]["Gia"].ToString()) + ((float)int.Parse(ds3.Rows[0]["SoNgayThue"].ToString()) * float.Parse(ds3.Rows[0]["Gia"].ToString()) * float.Parse(ds2.Rows[0]["HS_KhachThu3"].ToString())) * float.Parse(ds2.Rows[0]["HS_KhachNcNgoai"].ToString()) - float.Parse(ds3.Rows[0]["TraTruoc"].ToString());
                }
            }
            string sql4 = "UPDATE TONGTIEN SET TongTien = '" + tongtien + "'";
            DataTable ds4 = Config.AccessHelper.ExecuteQuery(sql4);

            string sql5 = "Select * from TONGTIEN";
            DataTable ds5 = Config.AccessHelper.ExecuteQuery(sql5);

            return Config.AccessHelper.ExecuteQuery(sql5);
        }

        
        //public ArrayList LayDanhSachHoaDon_ThanhToan()
        //{

        //    ArrayList arrDanhSach = new ArrayList();
        //    string sql = "Select*from HOADON_THANHTOAN";
        //    DataTable ds = Config.AccessHelper.ExecuteQuery(sql);
        //    HoaDon_ThanhToanDTO hd;
        //    int i = 0;
        //    int iSoDong = ds.Rows.Count;
        //    int iSoCot = ds.Columns.Count;
        //    while (i < iSoDong)
        //    {
        //        hd = new HoaDon_ThanhToanDTO();
        //        hd.SoHD = ds.Rows[i]["SoHD"].ToString();
        //        hd.TongTien = float.Parse(ds.Rows[i]["TongTien"].ToString());
        //        hd.NgayThanhToan = Convert.ToDateTime(ds.Rows[i]["NgayThanhToan"].ToString());
        //        hd.SoPDK = ds.Rows[i]["SoPDK"].ToString();
        //        hd.MasoNV = ds.Rows[i]["MasoNV"].ToString();
        //        arrDanhSach.Add(hd);
        //        i++;
        //    }
        //    return arrDanhSach;
        //}
    }
}
